package com.wh.db.sql;

import java.sql.Date;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Collection;
import java.util.UUID;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import com.wh.web.util.lang.StringUtil;

public final class SQLUtils {
	/**
	 * 去除hql的orderby 子句，用于pagedQuery.
	 */
	public static String getTableFromSQL(String sql) {
		int beginPos = sql.toLowerCase().indexOf("from");
		sql = sql.substring(beginPos + 5).trim();
		int len = sql.length();
		int i = 0;
		for (; i < len; i++) {
			if (Character.isWhitespace(sql.charAt(i))) break;
		}
		return sql.substring(0, i);
	}

	/**
	 * 去除hql的select 子句，未考虑union的情况,用于pagedQuery.
	 */
	public static String removeSelect(String sql) {
		int beginPos = sql.toLowerCase().indexOf("from");
		return sql.substring(beginPos);
	}

	/**
	 * 去除hql的orderby 子句，用于pagedQuery.
	 */
	public static String removeOrders(String hql) {
		Pattern p = Pattern.compile("order\\s*by[\\w|\\W|\\s|\\S]*", Pattern.CASE_INSENSITIVE);
		Matcher m = p.matcher(hql);
		StringBuffer sb = new StringBuffer();
		while (m.find()) {
			m.appendReplacement(sb, "");
		}
		m.appendTail(sb);
		return sb.toString();
	}

	/**
	 * 生成由问号(?)组成的字符串，这种用于字符串有特殊符号时传递sql参数使用
	 * 
	 * @param params
	 * @return
	 */
	public static String createQuestionMarks(int size) {
		size = size * 2;
		StringBuilder tmp = new StringBuilder(size);
		for (int i = 0; i < size; i++) {
			tmp.append("?,");
		}
		tmp.deleteCharAt(size - 1);
		return tmp.toString();
	}

	/**
	 * 生成由字符参数、单引号和逗号(?)连接而成的字符串，如果字符串中可能含有特殊符号（比如自身会含单引号、或有换行符等），不可采用此方式
	 * 
	 * @param params
	 * @return
	 */
	public static String createCommaSring(Collection<String> params) {
		if (params == null || params.size() == 0) return "";
		StringBuilder tmp = new StringBuilder();
		for (String s : params) {
			tmp.append('\'').append(s).append('\'').append(',');
		}
		tmp.deleteCharAt(tmp.length() - 1);
		return tmp.toString();
	}

	private final static DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

	public static String toSQLDate(java.util.Date d1) {
		return d1 == null ? null : df.format(d1);
	}

	public static java.sql.Time toSQLTime(java.util.Date d1) {
		return d1 == null ? null : new java.sql.Time(d1.getTime());
	}

	// 过滤通过页面表单提交的字符
	private static char[] SpecialSQLChars = { '\'', '<', '>', '%', '\\', ',', '.', '>', '=', '<', '-', '_', ';', '|', '[', ']', '&', '/', '-', '|', ' ' };
	private static boolean[] SpecialSQLChars2 = null;
	private static int startChar;
	static {
		Arrays.sort(SpecialSQLChars);
		startChar = SpecialSQLChars[0];
		int len1 = SpecialSQLChars.length;
		int len = SpecialSQLChars[len1 - 1] - startChar + 1;
		SpecialSQLChars2 = new boolean[len];
		for (int i = 0; i < len1; i++) {
			SpecialSQLChars2[SpecialSQLChars[i] - startChar] = true;
		}
	}

	//
	public static String delSpecialSQLChar(String sql) {
		StringBuilder sb = new StringBuilder(sql);
		for (int len = sb.length() - 1; len >= 0; len--) {
			int idx = sb.charAt(len) - startChar;
			if (idx > 0 && idx < SpecialSQLChars2.length && SpecialSQLChars2[idx]) sb.deleteCharAt(len);
		}
		return sb.toString();
	}

	public static String generateId() {
		return UUID.randomUUID().toString();
	}

	public static String getMD5SQLChar(String str) {
		str = delSpecialSQLChar(str);
		str = com.wh.util.security.MD5.encrypt(str);
		return str;
	}
}